package com.synchrodata.main;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.synchrodata.util.DataBaseUtil;

public class CreateTriggerMain {

	public static void main(String[] args) {
		System.out.println("开始执行创建同步列和触发器");
		new CreateTriggerMain().createTrigger();
		System.out.println("执行创建同步列和触发器完成");
	}

	/**
	 * 创建触发器
	 */
	public void createTrigger(){
		//创建字段
		createSyscCol(DataBaseUtil.getOneConnection());
		//这里只需要创建1个数据的库触发器，然后在导出库在导入新的用户中。这样2边数据库一致
		createAllTrigger(DataBaseUtil.getTwoConnection());
	}
    public void createSyscCol(Connection conn){
        List<String> delColSql = new ArrayList<String>();
        List<String> addColSql = new ArrayList<String>();

        //查询所有表和它的主键字段
        String sql = "select lower(col.table_name) table_name,lower(col.column_name) column_name from user_constraints con,user_cons_columns col "
                + " where con.constraint_name=col.constraint_name and con.constraint_type='P' "
                + " and col.table_name not like 'QRTZ_%' and col.table_name!='SYS_RECORD' "; //QRTZ_为定时任务的表,不生成触发器

        List<Map<String, Object>>  list = DataBaseUtil.query(conn, sql, null);
        if(list!=null && list.size()>0){
            for(Map<String, Object> map:list){
                String table_name = (String)map.get("table_name");

                String delCol = 	"  alter table "+table_name+" drop (is_sysc_data__)  ";
                System.out.println("delCol2===================="+delCol);
                delColSql.add(delCol);
                String addCol ="alter table "+table_name+" add is_sysc_data__ varchar2(10) null ";
                addColSql.add(addCol);
            }
        }

        for(String sql1:addColSql){
            DataBaseUtil.execute(conn, sql1);
        }
    }
/*	public void createSyscCol(Connection conn){
		List<String> delColSql = new ArrayList<String>();
		List<String> addColSql = new ArrayList<String>();

		//查询所有表和它的主键字段
		String sql = "select lower(col.table_name) table_name,lower(col.column_name) column_name from user_constraints con,user_cons_columns col "
				+ " where con.constraint_name=col.constraint_name and con.constraint_type='P' "
				+ " and col.table_name not like 'QRTZ_%' and col.table_name!='SYS_RECORD' "; //QRTZ_为定时任务的表,不生成触发器

		List<Map<String, Object>>  list = DataBaseUtil.query(conn, sql, null);
		if(list!=null && list.size()>0){
			for(Map<String, Object> map:list){
				String table_name = (String)map.get("table_name");

				String delCol = 	"  alter table "+table_name+" drop (is_sysc_data__)  ";
				System.out.println("delCol2===================="+delCol);
				delColSql.add(delCol);
				String addCol ="alter table "+table_name+" add is_sysc_data__ varchar2(10) null ";
				addColSql.add(addCol);
			}
		}

		for(String sql1:delColSql){
			DataBaseUtil.execute(conn, sql1);
		}
		for(String sql1:addColSql){
			DataBaseUtil.execute(conn, sql1);
		}
	}*/

	/**
	 * 根据连接对象,创建所有表的触发器
	 * @param conn
	 */
	public void createAllTrigger(Connection conn){

		List<String> batchSql = new ArrayList<String>();

		//查询所有表和它的主键字段
		String sql = "select lower(col.table_name) table_name,lower(col.column_name) column_name from user_constraints con,user_cons_columns col "
				+ " where con.constraint_name=col.constraint_name and con.constraint_type='P' "
				+ " and col.table_name not like 'QRTZ_%' and col.table_name!='SYS_RECORD' "; //QRTZ_为定时任务的表,不生成触发器
				//+ " and (col.table_name ='SYS_USER' or col.table_name ='EQ_FLAW' or col.table_name like 'ACT_%') ";

		List<Map<String, Object>>  list = DataBaseUtil.query(conn, sql, null);
		if(list!=null && list.size()>0){

			int i=1;
			for(Map<String, Object> map:list){
				String table_name = (String)map.get("table_name");
				String column_name = (String)map.get("column_name");

				//新增触发器
				String insertTriggerSql = "create or replace trigger record"+i+"_insert_trigger "
						+ " before insert on "+table_name+" for each row "
						+ " begin "
						+ " if :new.is_sysc_data__ ='Y' then"
						+ " :new.is_sysc_data__ :=null;"
						+ " else "
						+ " insert into sys_record(table_name,table_pkid,field_name,record_state,record_time) "
						+ " values('"+table_name+"',:new."+column_name+",'"+column_name+"','1',sysdate); "
						+ " end if;"
						+ " end; ";
				//修改触发器
				String updateTriggerSql ="create or replace trigger record"+i+"_update_trigger "
						+ " before update on "+table_name+" for each row "
						+ " declare count_num number; "
						+ " begin "
						+ " if :new.is_sysc_data__ ='Y' then"
						+ " :new.is_sysc_data__ :=null;"
						+ " else "
						+ " select count(1) into count_num from sys_record where table_name='"+table_name+"' and table_pkid=:old."+column_name+" and record_state='2'; "
						+ " if count_num >= 1 then "
						+ " update sys_record set record_time=sysdate where table_name='"+table_name+"' and table_pkid=:old."+column_name+" and record_state='2'; "
						+ " else "
						+ " insert into sys_record(table_name,table_pkid,field_name,record_state,record_time) values('"+table_name+"',:old."+column_name+",'"+column_name+"','2',sysdate); "
						+ " end if;"
						+ " end if; "
						+ " end; ";
				//删除触发器
				String deleteTriggerSql = "create or replace trigger record"+i+"_delete_trigger "
						+ " before delete on "+table_name+" for each row "
						+ " begin "
						+ " insert into sys_record(table_name,table_pkid,field_name,record_state,record_time) "
						+ " values('"+table_name+"',:old."+column_name+",'"+column_name+"','3',sysdate); "
						+ " end; ";

				batchSql.add(insertTriggerSql);
				batchSql.add(updateTriggerSql);
				batchSql.add(deleteTriggerSql);
				i++;
			}
			//执行批处理插入
			DataBaseUtil.sqlBatch(conn, batchSql);
		}
	}

	public void deleteAllTrigger(Connection conn){
		//查询所有业务表的监听
		String sql = "select object_name from user_objects where object_type='TRIGGER' "
				+ "and ( object_name like '%_DELETE_TRIGGER' or object_name like '%_UPDATE_TRIGGER' or object_name like '%_INSERT_TRIGGER')";
		List<Map<String, Object>>  list = DataBaseUtil.query(conn, sql, null);

		String delSql = "drop trigger ";
		if(list!=null && list.size()>0){
			for(Map<String, Object> map:list){
				DataBaseUtil.execute(conn, delSql+map.get("object_name").toString());
			}
		}
	}
}
